package com.itmk.student.dao.impl;

import com.itmk.common.BaseDao;
import com.itmk.student.dao.StudentDao;
import com.itmk.student.entity.*;
import com.itmk.teacher.entity.Teacher;
import com.itmk.teacher.entity.TeacherTotal;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl extends BaseDao implements StudentDao {
    @Override
    public void add(Student student) {
        //1、定义sql
        String sql = "insert into student(stu_id,calss_id,stu_name,phone,id_card,sex,stu_num,password,join_year) values(?,?,?,?,?,?,?,?,?)";
        //2、定义handler
        BeanHandler<Student> handler = new BeanHandler<>(Student.class);
        //3、封装参数
        Object[] parm  = {
             student.getStuId(),
             student.getCalssId(),
             student.getStuName(),
             student.getPhone(),
             student.getIdCard(),
             student.getSex(),
             student.getStuNum(),
             student.getPassword(),
             student.getJoinYear()
        };
        try {
            query.insert(sql,handler,parm);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<StudentVo> list(StudentParm studentParm) {
        //关键字的查询
        if(StringUtils.isNotEmpty(studentParm.getStuName())){
            //1、定义sql
            StringBuilder sb = new StringBuilder("select * from (select cl.colloge_name,mj.major_name,cs.calss_name,st.* ,ROWNUM rm from  student  st left join  classes cs  on st.calss_id = cs.calss_id left join major  mj on mj.major_id = cs.major_id left join colloge  cl on cl.colloge_id = mj.colloge_id)");
            sb.append(" where stu_name like '%" + studentParm.getStuName() + "%' ");
            //分页参数
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<StudentVo> handler = new BeanListHandler<>(StudentVo.class,processor);
            try {
                return query.query(sql,handler);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else {
            StringBuilder sb = new StringBuilder("select * from (select cl.colloge_name,mj.major_name,cs.calss_name,st.* ,ROWNUM rm from  student  st left join  classes cs  on st.calss_id = cs.calss_id left join major  mj on mj.major_id = cs.major_id left join colloge  cl on cl.colloge_id = mj.colloge_id)");
            sb.append(" where rm >= ? and rm <= ? ");
            //分页参数
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<StudentVo> handler = new BeanListHandler<>(StudentVo.class,processor);
            //3、定义参数
            Object[] parms = {
                    (studentParm.getPage()-1)*studentParm.getLimit(),
                    (studentParm.getPage()*studentParm.getLimit())
            };
            try {
                return query.query(sql,handler,parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int getCount(StudentParm studentParm) {
         //1、定义sql
        StringBuilder sb = new StringBuilder("select count(st.stu_id) from  student st left join  classes cs  on st.calss_id = cs.calss_id left join major  mj on mj.major_id = cs.major_id left join colloge  cl on cl.colloge_id = mj.colloge_id");
        //关键字的查询
        if(StringUtils.isNotEmpty(studentParm.getStuName())){
            sb.append(" where st.stu_name like '%" + studentParm.getStuName() + "%'");
        }
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler();

        try {
            return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Student getById(String stuId) {
        //1、定义sql
        String sql = "select * from student where stu_id=?";
        //2、定义handler
        BeanHandler<Student> handler = new BeanHandler<>(Student.class,processor);

        try {
            return query.query(sql,handler,stuId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateById(Student student) {
        //1、定义sql
        String sql = "update student set calss_id=?,stu_name=?,phone=?,id_card=?,sex=?,stu_num=?,password=?," +
                "join_year=? where stu_id=?";
        //2、定义参数
        Object[] parms = {
                student.getCalssId(),
                student.getStuName(),
                student.getPhone(),
                student.getIdCard(),
                student.getSex(),
                student.getStuNum(),
                student.getPassword(),
                student.getJoinYear(),
                student.getStuId()
        };
        try {
            query.update(sql,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void deleteById(String stuId) {
        //定义sql
        String sql = "delete from student  where stu_id=?";
        try {
            query.update(sql,stuId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Student login(String stuNum, String password) {
        //1、定义sql
        String sql = "select * from student where stu_num=? and password=?";
        //2、定义handler
        BeanHandler<Student> handler = new BeanHandler<>(Student.class,processor);
        //3、参数定义
        Object[] parms = {
                stuNum,
                password
        };
        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<StudentPointsVo> pointList(StudentParm parm) {
        //1、定义sql
        String sq = "select cs.course_name,sp.* from student_point as sp left join course  as cs on cs.course_id = sp" +
                ".course_id  where 1=1 ";
        StringBuilder sb = new StringBuilder(sq);
        //关键字的查询
        if(StringUtils.isNotEmpty(parm.getCourseName())){
            sb.append(" and  cs.course_name like '%" + parm.getCourseName() + "%'");
        }
        //条件拼接
        sb.append(" and sp.stu_id =?");
        //分页参数
        sb.append(" limit ?,?");
        String sql = sb.toString();
        //2、定义handler
        BeanListHandler<StudentPointsVo> handler = new BeanListHandler<>(StudentPointsVo.class,processor);
        //3、参数定义
        Object[] parms = {
                parm.getStuId(),
                (parm.getPage()-1)*parm.getLimit(),
                parm.getLimit()
        };
        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int pointCount(StudentParm parm) {
        //1、定义sql
        String sq = "select count(sp.point_id) from student_point as sp left join course  as cs on cs.course_id = sp" +
                ".course_id  where 1=1 ";
        StringBuilder sb = new StringBuilder(sq);
        //关键字的查询
        if(StringUtils.isNotEmpty(parm.getCourseName())){
            sb.append(" and  cs.course_name like '%" + parm.getCourseName() + "%'");
        }
        //条件拼接
        sb.append(" and sp.stu_id =?");
        String sql = sb.toString();
        //2定义handler
        ScalarHandler handler = new ScalarHandler();
        try {
           return Integer.valueOf(query.query(sql,handler,parm.getStuId()).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int getTotal() {
        String sql = "select count(stu_id) as stu_total from student ";
        ScalarHandler handler = new ScalarHandler();
        try {
           return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Student getUserByPas(int userId, String password) {
        String sql = "select * from student where stu_id =? and password=?";
        BeanHandler<Student> handler = new BeanHandler<>(Student.class,processor);
        Object[] parms = {
                userId,
                password
        };
        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void updatePassword(int userId, String password) {
        String sql = "update student set password=? where stu_id =?";
        Object[] parms = {
              password,
              userId
        };
        try {
            query.update(sql,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
